package com.newcapec.news.dao.impl;

import com.newcapec.news.dao.ChannelDao;
import com.newcapec.news.entity.Channel;
import com.newcapec.news.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description:
 * @Author: fujun
 * @Date : 2022/3/28 12:12
 * @Version : 1.0
 * @company 新开普电子股份有限公司
 */
public class ChannelDaoImpl implements ChannelDao {
    public void insertChannel(Channel chnl){
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("insert into news_chnl(NAME,SORT,CRTIME,UPTIME) values(?,?,now(),now())");
            stmt.setString(1, chnl.getName());
            stmt.setInt(2, chnl.getSort());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtil.closeAll(conn, stmt, null);
    }

    public void updateChannel(Channel chnl){
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("update news_chnl set NAME=?,SORT=?,UPTIME=now() where ID=?");
            stmt.setString(1, chnl.getName());
            stmt.setInt(2, chnl.getSort());
            stmt.setInt(3, chnl.getId());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtil.closeAll(conn, stmt, null);
    }

    public void deleteChannel(int id){
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("delete from news_chnl where ID=?");
            stmt.setInt(1, id);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtil.closeAll(conn, stmt, null);
    }

    public List<Channel> queryChannel(){
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Channel> list = new ArrayList<Channel>();
        try {
            stmt = conn.prepareStatement("select ID,NAME,SORT,CRTIME,UPTIME from news_chnl order by ID");

            rs = stmt.executeQuery();
            while(rs.next()){
                Channel chnl = new Channel();
                chnl.setId(rs.getInt(1));
                chnl.setName(rs.getString(2));
                chnl.setSort(rs.getInt(3));
                chnl.setCrtime(rs.getTimestamp(4));
                chnl.setUptime(rs.getTimestamp(5));
                list.add(chnl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtil.closeAll(conn, stmt, rs);
        return list;
    }

    @Override
    public List<Channel> queryChannel(int user_id) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Channel> list = new ArrayList<Channel>();
        try {
            stmt = conn.prepareStatement("select a.ID,a.NAME,a.SORT,a.CRTIME,a.UPTIME from news_chnl a, user_channel_relation b where a.ID=b.channel_id   and b.user_id = ?");

            stmt.setInt(1,user_id);
            rs = stmt.executeQuery();
            while(rs.next()){
                Channel chnl = new Channel();
                chnl.setId(rs.getInt(1));
                chnl.setName(rs.getString(2));
                chnl.setSort(rs.getInt(3));
                chnl.setCrtime(rs.getTimestamp(4));
                chnl.setUptime(rs.getTimestamp(5));
                list.add(chnl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtil.closeAll(conn, stmt, rs);
        return list;
    }

    public Channel getChannel(int id){
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement("select ID,NAME,SORT,CRTIME,UPTIME from sys_chnl where ID=?");
            stmt.setInt(1, id);

            rs = stmt.executeQuery();
            if(rs.next()){
                Channel chnl = new Channel();
                chnl.setId(rs.getInt(1));
                chnl.setName(rs.getString(2));
                chnl.setSort(rs.getInt(3));
                chnl.setCrtime(rs.getTimestamp(4));
                chnl.setUptime(rs.getTimestamp(5));
                return chnl;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtil.closeAll(conn, stmt, rs);
        return null;
    }
}
